import datetime
from datetime import date
from database import *
from sqlalchemy import func, literal_column, and_, or_


# app = Flask(__name__) # 不能重复声明

def getDateTime():
    return datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')


@app.route('/')
def hello_world():
    return 'Hello World. NFT'


@app.route('/updateNFTdesc', methods=['POST'])
def updateNFTdesc():
    link = request.form.get("origin_link")
    desc = request.form.get("description")
    NFT.query.filter(NFT.origin_link == link).update({"description": desc})
    db.session.commit()
    return {"error": "0"}


@app.route('/riskTrend', methods=['GET'])
def riskTrend():
    month_begin = datetime.date.today() - datetime.timedelta(days=29)
    today = datetime.date.today()
    subq = db.session.query(func.date_format(Association.modify_time, "%Y-%m-%d").label('date'), Association.nft_id). \
        group_by('date', Association.nft_id).having(
        literal_column('date').between(month_begin, datetime.date.today())).subquery()
    res_date = db.session.query(subq.c.date, func.count('*').label("num")).group_by(subq.c.date).all()
    print(res_date)
    date_map = dict()
    for item in res_date:
        date_map[str(item.date)] = item.num

    date_dict = []
    Sum = 0
    d = month_begin
    while d <= today:
        df = d.strftime('%Y-%m-%d')
        if df in date_map:
            Sum += date_map[df]
            date_dict.append({"date": df, "num": Sum})
        else:
            date_dict.append({"date": df, "num": "-"})
        d += datetime.timedelta(days=1)

    return {"error": "0", "result": date_dict}


@app.route('/recentlyAddedRisk', methods=['GET'])
def recentlyAddedRisk():
    week_ago = datetime.date.today() - datetime.timedelta(days=6)
    ddl = datetime.date.today() + datetime.timedelta(days=1)  # 明天的开始 才是今天的结束
    res_type = db.session.query(Risk.id, Risk.name, func.count(Association.nft_id).label("num")). \
        join(Association, Association.risk_id == Risk.id). \
        filter(Association.modify_time.between(week_ago, ddl)). \
        group_by(Risk.id).all()
    print(res_type)
    type_dict = []
    for item in res_type:
        type_dict.append({"risk_id": item.id, "risk_name": item.name, "num": item.num})

    subq = db.session.query(func.date_format(Association.modify_time, "%Y-%m-%d").label('date'), Association.nft_id). \
        group_by('date', Association.nft_id).having(
        literal_column('date').between(week_ago, ddl)).subquery()
    res_date = db.session.query(subq.c.date, func.count('*').label("num")).group_by(subq.c.date).all()
    print(res_date)

    date_map = dict()
    for item in res_date:
        date_map[str(item.date)] = item.num

    date_dict = []

    d = week_ago
    while d <= datetime.date.today():
        df = d.strftime('%Y-%m-%d')
        if df in date_map:
            date_dict.append({"date": df, "num": date_map[df]})
        else:
            date_dict.append({"date": df, "num": "-"})
        d += datetime.timedelta(days=1)

    # for item in res_date:
    #     date_dict.append({"date": item.date, "num": item.num})
    return {"error": "0", "results": {"week": date_dict, "risk": type_dict}}


@app.route('/riskNum', methods=['GET'])
def riskNum():
    res = db.session.query(Risk.id, Risk.name, func.count(Association.nft_id).label("num")). \
        join(Association, Association.risk_id == Risk.id).group_by(Risk.id).all()
    print(res)
    _dict = []
    for item in res:
        _dict.append({"risk_id": item.id, "risk_name": item.name, "num": item.num})

    total = db.session.query(Association.nft_id).group_by(Association.nft_id).count()

    return {"error": "0", "results": _dict, "total": total}


@app.route('/riskDistribution', methods=['GET'])
def riskDistribution():
    subq = db.session.query(NFT.id, NFT.platform).join(Association, Association.nft_id == NFT.id). \
        group_by(Association.nft_id).subquery()
    # 子查询 先将NFT的多种风险归并 再计算platform
    res = db.session.query(subq.c.platform, func.count(subq.c.id).label("num")).group_by(subq.c.platform).all()
    print(res)

    res_total = db.session.query(NFT.platform, func.count(NFT.id).label("total_num")).group_by(NFT.platform).all()
    plat_map = {}
    for item in res_total:
        plat_map[item.platform] = item.total_num

    _dict = []
    for item in res:
        _dict.append({"platform": item.platform, "num": item.num, "total_num": plat_map[item.platform]})

    return {"error": "0", "results": _dict}


@app.route('/riskList', methods=['GET'])
def riskList():
    risks = Risk.query.all()
    res_dict = []
    for item in risks:
        res_dict.append({"risk_id": item.id, "risk_name": item.name})
    return {"error": "0", "result": res_dict}


@app.route('/riskTag', methods=['POST'])
def riskTag():
    nft_id = int(request.form.get("nft_id"))
    risk_id = int(request.form.get("risk_id"))
    risk_probability = int(request.form.get("risk_probability"))
    res = Association.query.filter(Association.nft_id == nft_id, Association.risk_id == risk_id).first()
    if res is None:  # 不存在则添加
        db.session.add(Association(nft_id=nft_id, risk_id=risk_id, probability=risk_probability,
                                   modify_time=getDateTime()))
    else:
        res.probability = risk_probability
        res.modify_time = getDateTime()
        db.session.commit()
    return {"error": "0"}


@app.route('/riskDel', methods=['POST'])
def riskDel():
    nft_id = int(request.form.get("nft_id"))
    risk_id = int(request.form.get("risk_id"))
    res = Association.query.filter(Association.nft_id == nft_id, Association.risk_id == risk_id).first_or_404()
    db.session.delete(res)
    db.session.commit()
    return {"error": "0"}


@app.route('/getType', methods=['Get'])
def getType():
    res = db.session.query(NFT.type).group_by(NFT.type).all()
    res_dict = []
    for item in res:
        res_dict.append({"type": item.type})

    return {"error": "0", "result": res_dict}


@app.route('/getPlatform', methods=['Get'])
def getPlatform():
    res = db.session.query(NFT.platform).group_by(NFT.platform).all()
    res_dict = []
    for item in res:
        res_dict.append({"platform": item.platform})

    return {"error": "0", "result": res_dict}


@app.route('/nftDetail', methods=['Get'])  # 只要根据id查Association表 join Risk即可 最后加上NFT
def nftDetail():
    _id = int(request.args.get("id"))
    risks = db.session.query(Association.probability, Risk.name, Risk.id, Association.modify_time). \
        join(Risk, Risk.id == Association.risk_id).filter(Association.nft_id == _id).all()
    risks_dict = []
    for item in risks:
        risks_dict.append({"risk_id": item.id, "risk_name": item.name, "risk_probability": item.probability,
                           "risk_modify": str(item.modify_time)})

    nft = NFT.query.filter(NFT.id == _id).first()
    res_dict = nft.to_dict()
    res_dict["risks"] = risks_dict
    print(res_dict)
    return {"error": "0", "result": res_dict}


@app.route('/nftList', methods=['Get'])
def nftList():
    limit = request.args.get("limit", 10, type=int)
    offset = int(request.args.get("offset", 0, type=int))
    platform = request.args.get("platform")
    Type = request.args.get("type")
    collection = request.args.get("collection")
    author = request.args.get("author")
    nft_name = request.args.get("nft_name")
    isRisk = request.args.get("isRisk", type=int)
    print(isRisk)

    # 二次查询 否则联表太麻烦了
    risk_id = db.session.query(Association.nft_id).group_by(Association.nft_id).all()
    risk_list = [item.nft_id for item in risk_id]
    print(risk_list)

    SumQ = NFT.query.filter(Type is None or NFT.type == Type). \
        filter(platform is None or NFT.platform == platform). \
        filter(collection is None or NFT.collection == collection). \
        filter(author is None or NFT.author == author). \
        filter(nft_name is None or NFT.name == nft_name). \
        filter(
        or_(isRisk is None, and_(isRisk == 1, NFT.id.in_(risk_list)), and_(isRisk == 0, NFT.id.notin_(risk_list))))
    # print(SumQ)
    Sum = SumQ.count()
    result = SumQ.offset(offset).limit(limit).all()
    result_list = [item.to_dict() for item in result]
    print(result)
    # print(json.dumps(result_list))
    return {"error": "0", "sum": Sum, "results": result_list}


@app.route('/ntfAdd', methods=['POST'])
def nftAdd():
    # print(NFT.query.first().__dict__)
    collection = request.form.get("collection")
    name = request.form.get("name")
    author = request.form.get("author")
    Date = date.fromisoformat(request.form.get("date"))
    amount = int(request.form.get("amount"))
    Type = request.form.get("type")
    platform = request.form.get("platform")
    blockchain = request.form.get("blockchain")
    price = float(request.form.get("price"))
    contract_address = request.form.get("contract_address")
    token_id = request.form.get("token_id")
    token_std = request.form.get("token_std")
    content_url = request.form.get("content_url")
    description = request.form.get("description")
    token_id_link = request.form.get("token_id_link")
    contract_address_link = request.form.get("contract_address_link")
    origin_link = request.form.get("origin_link")

    nft = NFT(name=name,
              author=author,
              date=Date,
              amount=amount,
              type=Type,
              collection=collection,
              platform=platform,
              price=price,
              content_url=content_url,
              blockchain=blockchain,
              contract_address=contract_address,
              token_std=token_std,
              token_id=token_id,
              description=description,
              token_id_link=token_id_link,
              contract_address_link=contract_address_link,
              origin_link=origin_link,
              add_time=getDateTime()
              )
    db.session.add(nft)
    db.session.commit()

    return {"error": "0"}


if __name__ == '__main__':
    app.run(host="0.0.0.0", port=5000, debug=True)
